﻿/*Author：Lary Pan(itolmqj123@gmail.com)
 *Date  ：2016/8/14
 *Tips  ：数据库相关操作
 *         支持类类型  int/float/string/int[]/float[]/string[]/bool
 * */

using UnityEngine;

using System;
using System.Collections.Generic;
using Mono.Data.Sqlite;
using System.Reflection;

public class DBHelper
{
    private SqliteConnection dbConnection;
    private SqliteCommand dbCommand;
    private SqliteDataReader reader;

    public DBHelper()
    {
        _OpenDB(CGameDefine.Sqlite_PATH_PRFIX);
    }

    private void _OpenDB(string connectStr)
    {
        try
        {
            dbConnection = new SqliteConnection(connectStr);
            dbConnection.Open();
            Debugger.Log("Conencted to DB.");

            _ExecuteSQL("CREATE TABLE IF NOT EXISTS xxxx (key TEXT, value TEXT)");
        }
        catch (Exception e)
        {
            Debugger.Log(e.Message);
        }
    }
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void CloseDB()
    {
        //销毁Command
        if (dbCommand != null)
        {
            dbCommand.Cancel();
        }
        dbCommand = null;

        //销毁Reader
        if (reader != null)
        {
            reader.Close();
        }
        reader = null;

        //销毁Connection
        if (dbConnection != null)
        {
            dbConnection.Close();
        }
        dbConnection = null;
        Debugger.Log("Disconnected from DB.");
    }

    private SqliteDataReader _ExecuteSQL(string sqlQuery)
    {
        dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = sqlQuery;
        Debugger.Log("DB Execute SQL:{0}", sqlQuery);
        return dbCommand.ExecuteReader();
    }

    public string Query(string key)
    {
        string sql = string.Format("SELECT value FROM xxxx where key='{0}'", key);
        reader = _ExecuteSQL(sql);
        if (reader.Read())
        {
            string value = reader.GetString(0);
            Debugger.Log("query----->{0} {1}", key, value);
            return value;
        }
        return string.Empty;
    }


    public void Update(string key, string value)
    {
        string sql  = string.Format("SELECT count(*) FROM xxxx where key='{0}'", key);
        reader      = _ExecuteSQL(sql);

        if (reader.Read())
        {
            int num = reader.GetInt16(0);
            if (num == 0)
            {
                sql = string.Format("INSERT INTO xxxx(key, value) VALUES ('{0}', '{1}')", key, value);
            }
            else
            {
                sql = string.Format("UPDATE xxxx SET value='{0}' WHERE key='{1}'", value, key);
            }
            _ExecuteSQL(sql);
        }
    }


    /// <summary>
    /// 开始事务
    /// </summary>
    public void Begin()
    {
        _ExecuteSQL("BEGIN");
    }

    /// <summary>
    /// 提交事件
    /// </summary>
    public void Commit()
    {
        _ExecuteSQL("COMMIT");
    }
}

    /**
    /// <summary>
    /// 读取整张表格
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public List<T> ReadFullTable<T>() where T : EntityBase
    {
        List<T> listT = new List<T>();

        Type t = typeof(T);
        string sql = string.Format("SELECT * FROM {0}", t.Name);
        reader = ExecuteSQL(sql);

        FieldInfo[] infos = t.GetFields();
        T instance = null;
        while (reader.Read())
        {
            instance = Activator.CreateInstance<T>();
            foreach (FieldInfo info in infos)
            {
                string value = reader.GetString(reader.GetOrdinal(info.Name));
                if (typeof(int) == info.FieldType)
                {
                    info.SetValue(instance, Convert.ToInt32(value));
                }
                else if (typeof(float) == info.FieldType)
                {
                    info.SetValue(instance, float.Parse(value));
                }
                else if (typeof(bool) == info.FieldType)
                {
                    info.SetValue(instance, Convert.ToInt32(value) == 1);
                }
                else if (typeof(string) == info.FieldType)
                {
                    info.SetValue(instance, value);
                }
                else
                {
                    string[] strArr = value.Split(',');
                    int length = strArr.Length;
                    if (length == 0) continue;

                    if (typeof(int[]) == info.FieldType)
                    {
                        int[] arr = new int[length];
                        for (int i = 0; i < length; i++)
                        {
                            arr[i] = Convert.ToInt32(strArr[i]);
                        }
                        info.SetValue(instance, arr);
                    }
                    else if (typeof(float[]) == info.FieldType)
                    {
                        float[] arr = new float[length];
                        for (int i = 0; i < length; i++)
                        {
                            arr[i] = Convert.ToSingle(strArr[i]);
                        }
                        info.SetValue(instance, arr);
                    }
                    else if (typeof(string[]) == info.FieldType)
                    {
                        string[] arr = new string[length];
                        for (int i = 0; i < length; i++)
                        {
                            arr[i] = strArr[i].ToString();
                        }
                        info.SetValue(instance, arr);
                    }
                    else if (typeof(Vector3) == info.FieldType)
                    {
                        Vector3 vec = Vector3.zero;
                        if (length == 3)
                        {
                            vec.x = Convert.ToSingle(strArr[0]);
                            vec.y = Convert.ToSingle(strArr[1]);
                            vec.z = Convert.ToSingle(strArr[2]);
                        }
                        info.SetValue(instance, vec);
                    }
                }
            }
            listT.Add(instance as T);
        }
        return listT;
    }
    */
	

